﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using PMS.ENT;
using System.Data.SqlClient;
using PMS.Utilities;
using System.Configuration;


namespace PMS.DAL
{
    public class GroupDAL
    {
        #region Common
        private static GroupENT CreateGroupFromReader(IDataReader reader)
        {
            GroupENT item = new GroupENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.GroupID.ToString()))) item.GroupID = Utility.IntParse(reader[GroupColumns.GroupID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.GroupName.ToString()))) item.GroupName = Utility.StringParse(reader[GroupColumns.GroupName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.OwnerID.ToString()))) item.OwnerID = Utility.IntParse(reader[GroupColumns.OwnerID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.Status.ToString()))) item.Status = Utility.StringParse(reader[GroupColumns.Status.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[GroupColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[GroupColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[GroupColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[GroupColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[GroupColumns.Isdeleted.ToString()]);

                DataTable dt = reader.GetSchemaTable();
                if (Utility.FieldExist(dt, GroupColumns.Owner.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.Owner.ToString()))) item.Owner = Utility.StringParse(reader[GroupColumns.Owner.ToString()]);
                }
                if (Utility.FieldExist(dt, GroupColumns.ProjectID.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.ProjectID.ToString()))) item.ProjectID = Utility.IntParse(reader[GroupColumns.ProjectID.ToString()]);
                }
                if (Utility.FieldExist(dt, GroupColumns.ProjectName.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.ProjectName.ToString()))) item.ProjectName = Utility.StringParse(reader[GroupColumns.ProjectName.ToString()]);
                }
                if (Utility.FieldExist(dt, GroupColumns.ScheduleID.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.ScheduleID.ToString()))) item.ScheduleID = Utility.IntParse(reader[GroupColumns.ScheduleID.ToString()]);
                }
                if (Utility.FieldExist(dt, GroupColumns.ScheduleName.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.ScheduleName.ToString()))) item.ScheduleName = Utility.StringParse(reader[GroupColumns.ScheduleName.ToString()]);
                }
                if (Utility.FieldExist(dt, GroupColumns.Member.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.Member.ToString()))) item.Member = Utility.IntParse(reader[GroupColumns.Member.ToString()]);
                }
                if (Utility.FieldExist(dt, GroupColumns.SStatus.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(GroupColumns.SStatus.ToString()))) item.SStatus = Utility.StringParse(reader[GroupColumns.SStatus.ToString()]);
                }
            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static GroupENT GetGroupByID(int groupID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetByID.ToString());
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);

                GroupENT group = new GroupENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        group = CreateGroupFromReader(reader);
                    }
                    reader.Close();
                }

                return group;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetGroupByID");
            }
        }

        public static GroupCollection GetGroupList(string groupName, int semesterID, int projectID, int scheduleID, int userID, string status, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetList.ToString());
                GroupCollection listGroup = new GroupCollection();
                database.AddInParameter(dbCommand, "@GroupName", DbType.String, groupName);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listGroup.Add(CreateGroupFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetGroupList");
            }
        }

        public static GroupCollection GetJoinGroupList(int projectID, int semesterID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetJoinList.ToString());
                GroupCollection listGroup = new GroupCollection();
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listGroup.Add(CreateGroupFromReader(reader));
                    }
                    reader.Close();
                }


                return listGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetJoinGroupList");
            }
        }

        public static GroupCollection GetAcceptGroupList(int projectID, int semesterID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetAcceptList.ToString());
                GroupCollection listGroup = new GroupCollection();
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listGroup.Add(CreateGroupFromReader(reader));
                    }
                    reader.Close();
                }


                return listGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetAcceptGroupList");
            }
        }


        public static GroupCollection GetGroupListForCMB(int userID, int projectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetListForCMBForJoin.ToString());
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);

                GroupCollection listGroup = new GroupCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listGroup.Add(CreateGroupFromReader(reader));
                    }
                    reader.Close();
                }

                return listGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetGroupListForCMB");
            }
        }

        public static GroupCollection GetGroupListForCMB()
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetListForCMB.ToString());

                GroupCollection listGroup = new GroupCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listGroup.Add(CreateGroupFromReader(reader));
                    }
                    reader.Close();
                }

                return listGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetGroupListForCMB");
            }
        }

        public static GroupCollection GetGroupListByScheduleIDForCMB(int scheduleID, int semesterID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetListByScheduleIDForCMB.ToString());
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                GroupCollection listGroup = new GroupCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listGroup.Add(CreateGroupFromReader(reader));
                    }
                    reader.Close();
                }

                return listGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetGroupListByScheduleIDForCMB");
            }
        }

        public static GroupCollection GetGroupListJoinByScheduleIDForCMB(int scheduleID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetListJoinByScheduleID.ToString());
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);

                GroupCollection listGroup = new GroupCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listGroup.Add(CreateGroupFromReader(reader));
                    }
                    reader.Close();
                }

                return listGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetGroupListByScheduleIDForCMB");
            }
        }

        public static GroupCollection GetGroupListByStatus(string groupID, string status)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetListByStatus.ToString());
                database.AddInParameter(dbCommand, "@GroupID", DbType.String, groupID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);

                GroupCollection listGroup = new GroupCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listGroup.Add(CreateGroupFromReader(reader));
                    }
                    reader.Close();
                }

                return listGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetGroupListForCMB");
            }
        }


        public static GroupCollection GetGroupListByOwnerAndStatus(int userID, string status, string groupID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupGetListByOwnerAndStatus.ToString());

                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@GroupID", DbType.String, groupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                GroupCollection listGroup = new GroupCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listGroup.Add(CreateGroupFromReader(reader));
                    }
                    reader.Close();
                }

                return listGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetGroupListForCMB");
            }
        }
        #endregion

        #region Insert, Update, Delete

        public static int InsertGroup(GroupENT group, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupInsert.ToString());
                int groupID = 0;
                database.AddOutParameter(dbCommand, "@GroupID", DbType.Int32, group.GroupID);
                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, group.OwnerID);
                database.AddInParameter(dbCommand, "@GroupName", DbType.String, group.GroupName);
                database.AddInParameter(dbCommand, "@Status", DbType.String, group.Status);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, group.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, group.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, group.Isdeleted);

                database.ExecuteNonQuery(dbCommand, trans);
                groupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@GroupID"));
                return groupID;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertGroup");
            }
        }

        public static int InsertGroup(GroupENT group)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                using (DbConnection conn = database.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();

                    try
                    {
                        int leadtemp = 0;
                        int groupID = InsertGroup(group, trans);
                        if (groupID > 0)
                        {
                            foreach (UserGroupENT item in group.ListGroupMember)
                            {
                                //Visible để sd trigger , User leader sẽ confim bằng update bên dưới , [Không được sửa cấu trúc này] . Kiet

                                ////User leader mặc định confirm group
                                //if (item == group.ListGroupMember[0])
                                //{
                                //    item.Status = "Confirm";
                                //}
                                //else
                                //{
                                //    item.Status = "Waiting for Confirm";
                                //}
                                item.Status = "Waiting for Confirm";
                                item.GroupID = groupID;
                                item.CreatedBy = Utility.UserID;
                                item.UpdateBy = Utility.UserID;
                                int userGroupID = UserGroupDAL.InsertUserGroup(item, trans);
                                if (userGroupID <= 0)
                                {
                                    groupID = -2;
                                    trans.Rollback();
                                    return groupID;
                                }

                                if(item.UserID ==group.OwnerID){
                                    leadtemp = userGroupID;
                                }
                            }
                            
                            trans.Commit();
                            //Phần Update Confirm cho user leader
                            UserGroupDAL.ConfirmUserGroup(leadtemp.ToString(), Utility.UserID, "Confirm");
                        }
                        else
                        {
                            trans.Rollback();
                        }


                        return groupID;
                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        LogDAL.LogEx("BLM_ERR_InsertGroup", ex);
                        throw new DataAccessException("BLM_ERR_InsertGroup");
                    }
                    catch (DataAccessException)
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (ConfigurationException ex)
            {
                LogDAL.LogEx("BLM_ERR_Configuration", ex);
                throw new DataAccessException("BLM_ERR_Configuration");
            }
            catch (SqlException)
            {
                throw;
            }
            catch (DataAccessException)
            {
                throw;
            }
            catch (Exception ex)
            {
                LogDAL.LogEx("BLM_ERR_InsertGroup", ex);
                throw new DataAccessException("BLM_ERR_InsertGroup");
            }
        }

        public static int UpdateGroup(GroupENT group, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupUpdate.ToString());

                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, group.GroupID);
                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, group.OwnerID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, group.Status);
                database.AddInParameter(dbCommand, "@GroupName", DbType.String, group.GroupName);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, group.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, group.Isdeleted);

                database.ExecuteNonQuery(dbCommand, trans);

                int groupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@GroupID"));
                return groupID;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertGroup");
            }
        }

        public static int UpdateGroup(GroupENT group)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                using (DbConnection conn = database.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();

                    try
                    {
                        int groupID = UpdateGroup(group, trans);
                        if (groupID > 0)
                        {
                            string deletedID = string.Empty;

                            foreach (UserGroupENT item in group.ListGroupMember)
                            {
                                item.UpdateBy = Utility.UserID;
                                int id = item.UserGroupID;
                                if (id <= 0)
                                {
                                    item.GroupID = group.GroupID;
                                    int userGroupID = UserGroupDAL.InsertUserGroup(item, trans);
                                    if (userGroupID <= 0)
                                    {
                                        groupID = -2;
                                        trans.Rollback();
                                        return groupID;
                                    }
                                    else
                                    {
                                        
                                        deletedID += userGroupID + ",";
                                    }
                                }
                                else
                                {
                                    int userGroupID = UserGroupDAL.UpdateUserGroup(item, trans);
                                    if (userGroupID <= 0)
                                    {
                                        groupID = -3;
                                        trans.Rollback();
                                        return groupID;
                                    }
                                    else
                                    {
                                        deletedID += userGroupID + ",";
                                    }
                                }
                            }

                            if (deletedID.EndsWith(","))
                            {
                                deletedID = deletedID.Remove(deletedID.Length - 1);
                            }

                            UserGroupDAL.DeleteUserGroupNotInID(deletedID, groupID, Utility.UserID, trans);
                            trans.Commit();
                            return groupID;


                        }
                        else
                        {
                            trans.Rollback();
                        }


                        return groupID;
                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        LogDAL.LogEx("BLM_ERR_UpdateGroup", ex);
                        throw new DataAccessException("BLM_ERR_UpdateGroup");
                    }
                    catch (DataAccessException)
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (ConfigurationException ex)
            {
                LogDAL.LogEx("BLM_ERR_Configuration", ex);
                throw new DataAccessException("BLM_ERR_Configuration");
            }
            catch (SqlException)
            {
                throw;
            }
            catch (DataAccessException)
            {
                throw;
            }
            catch (Exception ex)
            {
                LogDAL.LogEx("BLM_ERR_UpdateGroup", ex);
                throw new DataAccessException("BLM_ERR_UpdateGroup");
            }
        }

        public static int DeleteGroup(string groupID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupDelete.ToString());
                database.AddInParameter(dbCommand, "@GroupID", DbType.String, groupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertGroup");
            }
        }


        public static int ConfirmGroup(string groupID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procGroupConfirm.ToString());
                database.AddInParameter(dbCommand, "@GroupID", DbType.String, groupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_ConfirmGroup");
            }
        }
        #endregion

        #region Validate

        public static bool CheckIsOwnAndDraft(int[] groupID, int userID)
        {
            try
            {
                if (groupID.Length == GetGroupListByOwnerAndStatus(userID, GroupStatus.Draft.ToString(), Utility.StringParse(groupID)).Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_CheckIsOwnAndDraft");
            }
        }

        public static bool CheckIsOwn(int[] groupID, int userID)
        {
            try
            {
                if (groupID.Length == GetGroupListByOwnerAndStatus(userID, string.Empty, Utility.StringParse(groupID)).Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_CheckIsOwn");
            }
        }

        public static bool CheckGroupStatus(int[] groupID, string status)
        {
            try
            {
                if (groupID.Length == GetGroupListByStatus(Utility.StringParse(groupID), status).Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_CheckGroupStatus");
            }
        }
        #endregion
    }
}
